#include "dataAnalsisByJThread.h"
#include <QDebug>
#include <mainwindow.h>

using namespace std;
DataAnalysisByJThread::DataAnalysisByJThread(QObject *parent): QObject{parent}
{
    analyseClient = new Analyse();
    derivedata1 = new derivedata();
    this->moveToThread(&thread);
    thread.start();
}

void DataAnalysisByJThread::run()
{
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString sql="Select CycleNum From AmplStep where ProgramId=(Select AmplGuid from ExperimentInfo) and IsCollect='是'";
    query.exec(sql);
    while (query.next()) {
        analyseClient->leh=query.value("CycleNum").toInt();
    }

    sql="Select Name From ItemCode Where DicCode=11 and IsDefault=1";
    query.exec(sql);
    while (query.next()) {
        neiAccess = query.value("Name").toString();
    }
    db.transaction();

    sql="DELETE FROM FluorescenceData WHERE rowid NOT IN (SELECT MIN(rowid) FROM FluorescenceData GROUP BY positionNumber, CyclesNumber)";
    query.exec(sql);

    sql=QString("Delete from FluorescenceLvData");
    query.exec(sql);

    sql=QString("Delete from FluorescenceLvboData");
    query.exec(sql);

    sql=QString("Delete from FluorescenceLogData");
    query.exec(sql);

    sql=QString("Delete from FluorescenceNormalData");
    query.exec(sql);

    sql=QString("Delete from FluorescenceLvboTempTable");
    query.exec(sql);

    sql=QString("Delete from FluorescenceTempTable");
    query.exec(sql);

    sql=QString("Delete from FluorescenceNormalTempTable");
    query.exec(sql);

    sql=QString("Delete from FluorescenceLogTempTable");
    query.exec(sql);

    sql=QString("Delete from FluorescenceResultTable");
    query.exec(sql);

    sql=QString("Insert into FluorescenceNormalData Select * from FluorescenceData");
    query.exec(sql);

    sql=QString("Insert into FluorescenceLvData Select * from FluorescenceData");
    query.exec(sql);

    sql=QString("Insert into FluorescenceLvboData Select * from FluorescenceData");
    query.exec(sql);

    sql=QString("Insert into FluorescenceLogData Select * from FluorescenceData");
    query.exec(sql);

    db.commit();

    for(int i=1;i<=96;i++){
        QVector<QPointF> fData;
        QVector<QPointF> rData;
        QVector<QPointF> cData;
        QVector<QPointF> vData;
        QString  sql=QString("select f.sampleId,FAM,ROX,CY5,VIC "
                             "From FluorescenceData f "
                             "where PositionNumber='%1' "
                             "order by CyclesNumber;").arg(QString::number(i));
        query.exec(sql);

        QVector<int> data;
        QString sampleId;
        int a=1;
        while (query.next()) {
            sampleId=query.value("SampleId").toString();
            QPointF fpoint=QPoint(a,query.value("FAM").toInt());
            fData.append(fpoint);
            QPointF rpoint=QPoint(a,query.value("ROX").toInt());
            rData.append(rpoint);
            QPointF cpoint=QPoint(a,query.value("CY5").toInt());
            cData.append(cpoint);
            QPointF vpoint=QPoint(a,query.value("VIC").toInt());
            vData.append(vpoint);
            a++;
        }

        if(!sampleId.isEmpty()){
            analyseAndSaveData(fData,sampleId,"FAM");
            analyseAndSaveData(rData,sampleId,"ROX");
            analyseAndSaveData(cData,sampleId,"CY5");
            analyseAndSaveData(vData,sampleId,"VIC");
        }
    }

    db.transaction();
    sql="Update FluorescenceNormalData set "
        "FAM=(Select AccessVal from FluorescenceNormalTempTable where FluorescenceNormalData.SampleId=FluorescenceNormalTempTable.SampleID and FluorescenceNormalData.CyclesNumber=FluorescenceNormalTempTable.CyclesNumber and FluorescenceNormalTempTable.AccessType='FAM'),"
        "VIC=(Select AccessVal from FluorescenceNormalTempTable where FluorescenceNormalData.SampleId=FluorescenceNormalTempTable.SampleID and FluorescenceNormalData.CyclesNumber=FluorescenceNormalTempTable.CyclesNumber and FluorescenceNormalTempTable.AccessType='VIC'),"
        "ROX=(Select AccessVal from FluorescenceNormalTempTable where FluorescenceNormalData.SampleId=FluorescenceNormalTempTable.SampleID and FluorescenceNormalData.CyclesNumber=FluorescenceNormalTempTable.CyclesNumber and FluorescenceNormalTempTable.AccessType='ROX'),"
        "CY5=(Select AccessVal from FluorescenceNormalTempTable where FluorescenceNormalData.SampleId=FluorescenceNormalTempTable.SampleID and FluorescenceNormalData.CyclesNumber=FluorescenceNormalTempTable.CyclesNumber and FluorescenceNormalTempTable.AccessType='CY5')";
    query.exec(sql);

    sql="Update FluorescenceLvboData set "
        "FAM=(Select AccessVal from FluorescenceLvboTempTable where FluorescenceLvboData.SampleId=FluorescenceLvboTempTable.SampleID and FluorescenceLvboData.CyclesNumber=FluorescenceLvboTempTable.CyclesNumber and FluorescenceLvboTempTable.AccessType='FAM'),"
        "VIC=(Select AccessVal from FluorescenceLvboTempTable where FluorescenceLvboData.SampleId=FluorescenceLvboTempTable.SampleID and FluorescenceLvboData.CyclesNumber=FluorescenceLvboTempTable.CyclesNumber and FluorescenceLvboTempTable.AccessType='VIC'),"
        "ROX=(Select AccessVal from FluorescenceLvboTempTable where FluorescenceLvboData.SampleId=FluorescenceLvboTempTable.SampleID and FluorescenceLvboData.CyclesNumber=FluorescenceLvboTempTable.CyclesNumber and FluorescenceLvboTempTable.AccessType='ROX'),"
        "CY5=(Select AccessVal from FluorescenceLvboTempTable where FluorescenceLvboData.SampleId=FluorescenceLvboTempTable.SampleID and FluorescenceLvboData.CyclesNumber=FluorescenceLvboTempTable.CyclesNumber and FluorescenceLvboTempTable.AccessType='CY5')";
    query.exec(sql);

    sql="Update FluorescenceLvData set "
        "FAM=(Select AccessVal from FluorescenceTempTable where FluorescenceLvData.SampleId=FluorescenceTempTable.SampleID and FluorescenceLvData.CyclesNumber=FluorescenceTempTable.CyclesNumber and FluorescenceTempTable.AccessType='FAM'),"
        "VIC=(Select AccessVal from FluorescenceTempTable where FluorescenceLvData.SampleId=FluorescenceTempTable.SampleID and FluorescenceLvData.CyclesNumber=FluorescenceTempTable.CyclesNumber and FluorescenceTempTable.AccessType='VIC'),"
        "ROX=(Select AccessVal from FluorescenceTempTable where FluorescenceLvData.SampleId=FluorescenceTempTable.SampleID and FluorescenceLvData.CyclesNumber=FluorescenceTempTable.CyclesNumber and FluorescenceTempTable.AccessType='ROX'),"
        "CY5=(Select AccessVal from FluorescenceTempTable where FluorescenceLvData.SampleId=FluorescenceTempTable.SampleID and FluorescenceLvData.CyclesNumber=FluorescenceTempTable.CyclesNumber and FluorescenceTempTable.AccessType='CY5')";
    query.exec(sql);

    sql="Update FluorescenceLogData set "
        "FAM=(Select AccessVal from FluorescenceLogTempTable where FluorescenceLogData.SampleId=FluorescenceLogTempTable.SampleID and FluorescenceLogData.CyclesNumber=FluorescenceLogTempTable.CyclesNumber and FluorescenceLogTempTable.AccessType='FAM'),"
        "VIC=(Select AccessVal from FluorescenceLogTempTable where FluorescenceLogData.SampleId=FluorescenceLogTempTable.SampleID and FluorescenceLogData.CyclesNumber=FluorescenceLogTempTable.CyclesNumber and FluorescenceLogTempTable.AccessType='VIC'),"
        "ROX=(Select AccessVal from FluorescenceLogTempTable where FluorescenceLogData.SampleId=FluorescenceLogTempTable.SampleID and FluorescenceLogData.CyclesNumber=FluorescenceLogTempTable.CyclesNumber and FluorescenceLogTempTable.AccessType='ROX'),"
        "CY5=(Select AccessVal from FluorescenceLogTempTable where FluorescenceLogData.SampleId=FluorescenceLogTempTable.SampleID and FluorescenceLogData.CyclesNumber=FluorescenceLogTempTable.CyclesNumber and FluorescenceLogTempTable.AccessType='CY5')";
    query.exec(sql);

    sql="update SampleAccessInfo set CtValue=(select case when CTVal>0 then CTVal else 'nan' end from  FluorescenceResultTable f "
        "where SampleAccessInfo.SampleGuid=f.SampleID and "
        "SampleAccessInfo.AccessName= f.AccessType )";
    query.exec(sql);

    db.commit();

    QFile file(MainWindow::experimentSaveDataPath);

    file.open(QFile::WriteOnly|QFile::Text|QIODevice::Append);

    QTextStream out(&file);  //QTextStream会自动将 Unicode 编码同操作系统的编码进行转换

    sql="Select * From FluorescenceData";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceData(SampleId,PositionNumber,CyclesNumber,FAM,VIC,ROX,CY5) Values ('%1','%2','%3','%4','%5','%6','%7')").arg(query.value("SampleId").toString(),query.value("PositionNumber").toString(),
                                                                                                                                                                     query.value("CyclesNumber").toString(),query.value("FAM").toString(),
                                                                                                                                                                     query.value("VIC").toString(),query.value("ROX").toString(),
                                                                                                                                                                     query.value("CY5").toString());
        out<<addSql+"\n";
    }
    sql="Select * From FluorescenceLvData";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceLvData(SampleId,PositionNumber,CyclesNumber,FAM,VIC,ROX,CY5) Values ('%1','%2','%3','%4','%5','%6','%7')").arg(query.value("SampleId").toString(),query.value("PositionNumber").toString(),
                                                                                                                                                                       query.value("CyclesNumber").toString(),query.value("FAM").toString(),
                                                                                                                                                                       query.value("VIC").toString(),query.value("ROX").toString(),
                                                                                                                                                                       query.value("CY5").toString());
        out<<addSql+"\n";
    }

    sql="Select * From FluorescenceLvBoData";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceLvBoData(SampleId,PositionNumber,CyclesNumber,FAM,VIC,ROX,CY5) Values ('%1','%2','%3','%4','%5','%6','%7')").arg(query.value("SampleId").toString(),query.value("PositionNumber").toString(),
                                                                                                                                                                         query.value("CyclesNumber").toString(),query.value("FAM").toString(),
                                                                                                                                                                         query.value("VIC").toString(),query.value("ROX").toString(),
                                                                                                                                                                         query.value("CY5").toString());
        out<<addSql+"\n";
    }

    sql="Select * From FluorescenceNormalData";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceNormalData(SampleId,PositionNumber,CyclesNumber,FAM,VIC,ROX,CY5) Values ('%1','%2','%3','%4','%5','%6','%7')").arg(query.value("SampleId").toString(),query.value("PositionNumber").toString(),
                                                                                                                                                                           query.value("CyclesNumber").toString(),query.value("FAM").toString(),
                                                                                                                                                                           query.value("VIC").toString(),query.value("ROX").toString(),
                                                                                                                                                                           query.value("CY5").toString());
        out<<addSql+"\n";
    }

    sql="Select * From FluorescenceLogData";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceLogData(SampleId,PositionNumber,CyclesNumber,FAM,VIC,ROX,CY5) Values ('%1','%2','%3','%4','%5','%6','%7')").arg(query.value("SampleId").toString(),query.value("PositionNumber").toString(),
                                                                                                                                                                        query.value("CyclesNumber").toString(),query.value("FAM").toString(),
                                                                                                                                                                        query.value("VIC").toString(),query.value("ROX").toString(),
                                                                                                                                                                        query.value("CY5").toString());
        out<<addSql+"\n";
    }

    sql="Select * From FluorescenceResultTable";
    query.exec(sql);
    while (query.next()) {
        QString addSql=QString("Insert into FluorescenceResultTable Values ('%1','%2','%3','%4','%5','%6','%7','%8')").arg(query.value("SampleID").toString(),query.value("AccessType").toString(),
                                                                                                                           query.value("BaselineValue").toString(),query.value("Threshold").toString(),
                                                                                                                           query.value("Efficiency").toString(),query.value("Slope").toString(),
                                                                                                                           query.value("CTVal").toString(),query.value("StandardDeviation").toString());
        out<<addSql+"\n";
    }
    sql="update SampleAccessInfo set CtValue=(select case when CTVal>0 then CTVal else 'nan' end from  FluorescenceResultTable f "
        "where SampleAccessInfo.SampleGuid=f.SampleID and "
        "SampleAccessInfo.AccessName= f.AccessType )";
    out<<sql + "\n" ;

    file.close();

#ifdef Q_OS_ANDROID
//    FtpHelpers *ftp=new FtpHelpers();
//    ftp->uploadFile(MainWindow::experimentSaveDataPath);
#endif

    derivedata1 = new derivedata();
    derivedata1->refOriginalCurveTable();
    derivedata1->refAmplificationCurveTable();
    derivedata1->standardCurveChart();
    derivedata1->guide();
    derivedata1->datao();
    thread.quit();
}

//计算并保存数据
void DataAnalysisByJThread::analyseAndSaveData( QVector<QPointF> ying,QString sampleId,QString accessType){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    db.transaction();

    QPCRModel::Model *sigmoid;
    sigmoid=new QPCRModel::Model();

    QVector<QPointF> lvboData= sigmoid->filter(ying);

    for(int i=0;i<lvboData.length();i++){
        QString sql=QString("Insert Into FluorescenceLvboTempTable values('%1','%2','%3','%4')").arg(sampleId,QString::number(i+1),accessType,QString::number(lvboData[i].y()));
        query.exec(sql);
    }
    sigmoid->setRawData(lvboData);


    sigmoid->startCalculation();
    QVector<QPointF> lvData = sigmoid->getFittedData_delta();

    for(int i=1;i<lvData.length();i++){
        QString sql=QString("Insert Into FluorescenceTempTable values('%1','%2','%3','%4')").arg(sampleId,QString::number(i),accessType,QString::number(lvData[i].y()));
        query.exec(sql);
    }

    QString baseLineVal =QString::number(sigmoid->getBgValue());
    QString Efficiency = QString::number(sigmoid->getEfficiency());
    QString Slope = QString::number(sigmoid->getSlope());
    QString StandardDeviation="";
    QString Threshold ="";
    QString RnThreshold="";
    QString logThreshold="";
    QString logRnThreshold="";
    QString normalThreshold="";

    double Ct=0;

    Ct=sigmoid->getCt();

    QPCRModel::Model::YAxisType axisType=static_cast<QPCRModel::Model::YAxisType>(1);

    Threshold = QString::number(sigmoid->getThreshold(axisType));


    QString sql=QString("Insert into FluorescenceResultTable values('%1','%2','%3','%4',"
                        "'%5','%6','%7','%8')").arg(sampleId,accessType,baseLineVal,Threshold,Efficiency,Slope,
                                                                           QString::number(Ct,'f',2),StandardDeviation);
    query.exec(sql);


    QVector<QPointF> normalData = sigmoid->getFittedData_norm();
    for(int i=1;i<normalData.length();i++){
        QString sql=QString("Insert Into FluorescenceNormalTempTable values('%1','%2','%3','%4')").arg(sampleId,QString::number(i),accessType,QString::number(normalData[i].y()));
        query.exec(sql);
    }

    QVector<QPointF> logData = sigmoid->getFittedData_log_delta();

    for(int i=1;i<logData.length();i++){
        QString sql=QString("Insert Into FluorescenceLogTempTable values('%1','%2','%3','%4')").arg(sampleId,QString::number(i),accessType,QString::number(logData[i].y()));
        query.exec(sql);
    }

    db.commit();
}

QString DataAnalysisByJThread::GetDateString(int val){
    return QString::number(val).length()==2?QString::number(val): "0"+QString::number(val);
}
